package com.employee.dao;

import com.employee.entity.Employee;
import com.employee.util.DBUtil;
import com.employee.util.IdGenerator;
import com.employee.util.ResultSetMapper;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 员工表DAO
 */
public class EmployeeDao {
    
    /**
     * 分页查询员工（支持多条件组合查询）
     * @param pageNum 页码
     * @param pageSize 每页大小
     * @param name 姓名（模糊查询）
     * @param phone 手机号（模糊查询）
     * @param gender 性别（精确查询）
     * @param positionType 岗位类型（精确查询）
     * @param hireDateStart 入职开始日期
     * @param hireDateEnd 入职结束日期
     * @param salaryMin 最低薪资
     * @param salaryMax 最高薪资
     */
    public Map<String, Object> findByPage(int pageNum, int pageSize, String name, String phone, 
                                         String gender, String positionType, Date hireDateStart, 
                                         Date hireDateEnd, BigDecimal salaryMin, BigDecimal salaryMax) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        PreparedStatement countStmt = null;
        ResultSet rs = null;
        ResultSet countRs = null;
        
        Map<String, Object> result = new HashMap<>();
        
        try {
            conn = DBUtil.getConnection();
            
            // 构建查询条件
            StringBuilder whereSql = new StringBuilder(" WHERE 1=1 ");
            List<Object> params = new ArrayList<>();
            
            if (name != null && !name.trim().isEmpty()) {
                whereSql.append(" AND name LIKE ? ");
                params.add("%" + name.trim() + "%");
            }
            
            if (phone != null && !phone.trim().isEmpty()) {
                whereSql.append(" AND phone LIKE ? ");
                params.add("%" + phone.trim() + "%");
            }
            
            if (gender != null && !gender.trim().isEmpty()) {
                whereSql.append(" AND gender = ? ");
                params.add(gender.trim());
            }
            
            if (positionType != null && !positionType.trim().isEmpty()) {
                whereSql.append(" AND position_type = ? ");
                params.add(positionType.trim());
            }
            
            if (hireDateStart != null) {
                whereSql.append(" AND hire_date >= ? ");
                params.add(new java.sql.Date(hireDateStart.getTime()));
            }
            
            if (hireDateEnd != null) {
                whereSql.append(" AND hire_date <= ? ");
                params.add(new java.sql.Date(hireDateEnd.getTime()));
            }
            
            if (salaryMin != null) {
                whereSql.append(" AND salary >= ? ");
                params.add(salaryMin);
            }
            
            if (salaryMax != null) {
                whereSql.append(" AND salary <= ? ");
                params.add(salaryMax);
            }
            
            // 查询总记录数
            String countSql = "SELECT COUNT(*) FROM t_employee" + whereSql.toString();
            countStmt = conn.prepareStatement(countSql);
            for (int i = 0; i < params.size(); i++) {
                countStmt.setObject(i + 1, params.get(i));
            }
            countRs = countStmt.executeQuery();
            int total = 0;
            if (countRs.next()) {
                total = countRs.getInt(1);
            }
            
            // 分页查询数据
            String sql = "SELECT * FROM t_employee" + whereSql.toString() + 
                        " ORDER BY create_time DESC LIMIT ?, ?";
            pstmt = conn.prepareStatement(sql);
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(i + 1, params.get(i));
            }
            pstmt.setInt(params.size() + 1, (pageNum - 1) * pageSize);
            pstmt.setInt(params.size() + 2, pageSize);
            
            rs = pstmt.executeQuery();
            List<Employee> list = ResultSetMapper.map(rs, Employee.class);
            
            result.put("list", list);
            result.put("total", total);
            result.put("pageNum", pageNum);
            result.put("pageSize", pageSize);
            
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(null, countStmt, countRs);
            DBUtil.close(conn, pstmt, rs);
        }
        
        return result;
    }
    
    /**
     * 生成员工编号
     * 规则：岗位编号 + 入职年份 + 数字（从0001开始）
     * 例如：DEV20230001
     */
    public String generateEmpNo(String positionCode, Date hireDate) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            conn = DBUtil.getConnection();
            
            // 获取入职年份
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
            String year = sdf.format(hireDate);
            
            // 构建编号前缀
            String prefix = positionCode + year;
            
            // 查询该前缀下已有的最大编号
            String sql = "SELECT MAX(emp_no) FROM t_employee WHERE emp_no LIKE ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, prefix + "%");
            
            rs = pstmt.executeQuery();
            
            int nextNum = 1;
            if (rs.next()) {
                String maxEmpNo = rs.getString(1);
                if (maxEmpNo != null && maxEmpNo.length() > prefix.length()) {
                    String numStr = maxEmpNo.substring(prefix.length());
                    try {
                        nextNum = Integer.parseInt(numStr) + 1;
                    } catch (NumberFormatException e) {
                        nextNum = 1;
                    }
                }
            }
            
            // 生成新编号，数字部分补足4位
            return prefix + String.format("%04d", nextNum);
            
        } catch (Exception e) {
            e.printStackTrace();
            // 如果出错，使用时间戳作为后缀
            return positionCode + System.currentTimeMillis();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
    }
    
    /**
     * 检查员工编号是否已存在
     */
    public boolean isEmpNoExists(String empNo) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT COUNT(*) FROM t_employee WHERE emp_no = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, empNo);
            
            rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
            
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
        
        return false;
    }
    
    /**
     * 根据ID查询员工
     */
    public Employee findById(String id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT * FROM t_employee WHERE id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, id);
            
            rs = pstmt.executeQuery();
            List<Employee> list = ResultSetMapper.map(rs, Employee.class);
            return list.isEmpty() ? null : list.get(0);
            
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
    }
    
    /**
     * 新增员工
     */
    public boolean insert(Employee employee) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        try {
            conn = DBUtil.getConnection();
            String sql = "INSERT INTO t_employee (id, emp_no, name, age, gender, phone, " +
                        "hire_date, position_type, position_code, salary) " +
                        "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            pstmt = conn.prepareStatement(sql);
            
            // 生成主键ID
            employee.setId(IdGenerator.generateId("EMP"));
            
            // 生成员工编号
            String empNo = generateEmpNo(employee.getPositionCode(), employee.getHireDate());
            employee.setEmpNo(empNo);
            
            pstmt.setString(1, employee.getId());
            pstmt.setString(2, employee.getEmpNo());
            pstmt.setString(3, employee.getName());
            pstmt.setInt(4, employee.getAge());
            pstmt.setString(5, employee.getGender());
            pstmt.setString(6, employee.getPhone());
            pstmt.setDate(7, new java.sql.Date(employee.getHireDate().getTime()));
            pstmt.setString(8, employee.getPositionType());
            pstmt.setString(9, employee.getPositionCode());
            pstmt.setBigDecimal(10, employee.getSalary());
            
            return pstmt.executeUpdate() > 0;
            
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DBUtil.close(conn, pstmt);
        }
    }
    
    /**
     * 更新员工
     */
    public boolean update(Employee employee) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        try {
            conn = DBUtil.getConnection();
            String sql = "UPDATE t_employee SET name=?, age=?, gender=?, phone=?, " +
                        "hire_date=?, position_type=?, position_code=?, salary=? " +
                        "WHERE id=?";
            pstmt = conn.prepareStatement(sql);
            
            pstmt.setString(1, employee.getName());
            pstmt.setInt(2, employee.getAge());
            pstmt.setString(3, employee.getGender());
            pstmt.setString(4, employee.getPhone());
            pstmt.setDate(5, new java.sql.Date(employee.getHireDate().getTime()));
            pstmt.setString(6, employee.getPositionType());
            pstmt.setString(7, employee.getPositionCode());
            pstmt.setBigDecimal(8, employee.getSalary());
            pstmt.setString(9, employee.getId());
            
            return pstmt.executeUpdate() > 0;
            
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DBUtil.close(conn, pstmt);
        }
    }
    
    /**
     * 删除员工
     */
    public boolean delete(String id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        try {
            conn = DBUtil.getConnection();
            String sql = "DELETE FROM t_employee WHERE id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, id);
            
            return pstmt.executeUpdate() > 0;
            
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DBUtil.close(conn, pstmt);
        }
    }
    
    /**
     * 批量删除员工
     */
    public boolean deleteBatch(String[] ids) {
        if (ids == null || ids.length == 0) {
            return false;
        }
        
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        try {
            conn = DBUtil.getConnection();
            StringBuilder sql = new StringBuilder("DELETE FROM t_employee WHERE id IN (");
            for (int i = 0; i < ids.length; i++) {
                sql.append("?");
                if (i < ids.length - 1) {
                    sql.append(",");
                }
            }
            sql.append(")");
            
            pstmt = conn.prepareStatement(sql.toString());
            for (int i = 0; i < ids.length; i++) {
                pstmt.setString(i + 1, ids[i]);
            }
            
            return pstmt.executeUpdate() > 0;
            
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DBUtil.close(conn, pstmt);
        }
    }
}
